import sqlite3
import os
from pathlib import Path
import glob

class InstanceDatabase:
    def __init__(self, db_path):
        """Initialize database connection"""
        self.db_path = db_path
        if not os.path.exists(db_path):
            raise FileNotFoundError(f"Database file not found: {db_path}")
        self.conn = sqlite3.connect(db_path)
        self.conn.row_factory = sqlite3.Row
    
    def close(self):
        """Close database connection"""
        if self.conn:
            self.conn.close()
    
    def __enter__(self):
        return self
    
    def __exit__(self, exc_type, exc_val, exc_tb):
        print("exit without closing database connection")
        # self.close()
    
    def get_available_tracks(self):
        """Get set of available tracks in the database"""
        cursor = self.conn.cursor()
        cursor.execute("SELECT value FROM track")
        return {row['value'] for row in cursor.fetchall()}
    
    def get_track_uuids(self, track_name):
        """Get hash codes for files in the specified track."""
        cursor = self.conn.cursor()
        
        query = """
        SELECT f.hash 
        FROM features f 
        LEFT JOIN track t ON f.hash = t.hash 
        WHERE t.value LIKE ?
        """
        cursor.execute(query, (track_name,))
        hashes = {row[0] for row in cursor.fetchall()}
        return hashes

    def add_track(self, track_name):
        """Add track to database if it doesn't exist and return its ID"""
        cursor = self.conn.cursor()
        try:
            cursor.execute("INSERT OR IGNORE INTO tracks (name) VALUES (?)", (track_name,))
            self.conn.commit()
            cursor.execute("SELECT id FROM tracks WHERE name = ?", (track_name,))
            return cursor.fetchone()[0]
        finally:
            cursor.close()

    def populate_from_directory(self, instance_dir, track_name):
        """Populate database with instance information from a directory"""
        if not os.path.exists(instance_dir):
            print(f"Error: Instance directory {instance_dir} does not exist")
            return
        
        cursor = self.conn.cursor()
        try:
            # Get or create track ID
            track_id = self.add_track(track_name)
            
            # Find all instance files (both .cnf and .cnf.xz)
            instance_files = []
            for ext in ['*.cnf', '*.cnf.xz']:
                instance_files.extend(glob.glob(os.path.join(instance_dir, '**', ext), recursive=True))
            
            print(f"Found {len(instance_files)} instance files")
            
            # Process each instance
            for filepath in instance_files:
                filename = os.path.basename(filepath)
                # Extract UUID from filename (part before first ~ or .)
                uuid = filename.split('~')[0].split('.')[0]
                
                # Add instance
                cursor.execute("""
                    INSERT OR REPLACE INTO instances 
                    (uuid, track_id)
                    VALUES (?, ?)
                """, (uuid, track_id))
            
            self.conn.commit()
            print(f"Successfully populated database with instances from {track_name}")
            
        except sqlite3.Error as e:
            print(f"Error populating database: {e}")
            self.conn.rollback()
        finally:
            cursor.close() 